﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace StartStop.Helpers
{

    public class Trophy
    {
        public const string LiveConnectionString = "data source=v2.startstop.me;initial catalog=startstop;persist security info=True;user id=sa;password=PASSWORD;";

        #region Privatefunctions
        private static ServiceResources.Trophy MapRowToTrophy(DataRow oRow)
        {
            ServiceResources.Trophy oTrophy = new ServiceResources.Trophy();


            oTrophy.Condition = oRow["tCondition"].ToString();
            oTrophy.Name = oRow["tName"].ToString();
            oTrophy.Description = oRow["tDescription"].ToString();
            oTrophy.TrophyID = int.Parse(oRow["TrophyID"].ToString());

            return oTrophy;
        }

        #endregion

        #region Return Trophies



        public static List<ServiceResources.Trophy> RecentTrophies(int NumberToReturn)
        {
            List<ServiceResources.Trophy> oTrophies = new List<ServiceResources.Trophy>();

            SqlConnection oConn = new SqlConnection(LiveConnectionString);
            oConn.Open();
            SqlCommand cmd = new SqlCommand("select Top " + NumberToReturn + " * from Trophies where approved = 1 order by CreatedOn desc", oConn);
            SqlDataAdapter da = new SqlDataAdapter();
            DataTable _returnDataTable = new DataTable();
            da.SelectCommand = cmd;
            da.Fill(_returnDataTable);

            foreach (DataRow oRow in _returnDataTable.Rows)
            {
                ServiceResources.Trophy oTrophy = MapRowToTrophy(oRow);
                oTrophies.Add(oTrophy);
            }

            oConn.Close();
            return oTrophies;
        }

        public static List<ServiceResources.Trophy> ListOfTrophies()
        {
            List<ServiceResources.Trophy> oTrophies = new List<ServiceResources.Trophy>();

            SqlConnection oConn = new SqlConnection(LiveConnectionString);
            oConn.Open();
            SqlCommand cmd = new SqlCommand("select  * from Trophies where approved = 1 order by CreatedOn desc", oConn);
            SqlDataAdapter da = new SqlDataAdapter();
            DataTable _returnDataTable = new DataTable();
            da.SelectCommand = cmd;
            da.Fill(_returnDataTable);

            foreach (DataRow oRow in _returnDataTable.Rows)
            {
                ServiceResources.Trophy oTrophy = MapRowToTrophy(oRow);


                oTrophies.Add(oTrophy);
            }

            oConn.Close();
            return oTrophies;
        }

        public static List<ServiceResources.Trophy> SpecificListOfTrophies(int IDtoMatch, int Type)
        {

            List<ServiceResources.Trophy> oTrophies = new List<ServiceResources.Trophy>();

            SqlConnection oConn = new SqlConnection(LiveConnectionString);
            oConn.Open();
            SqlCommand cmd = new SqlCommand();

            if (Type == 1) // It's an app
            {
                cmd = new SqlCommand(@"
                    select t.TrophyID, t.AppID, td.*
                    from [WindowAppTrophyLink] t 
                    inner join Trophies td on t.TrophyID = td.TrophyID
                    where t.AppID= " + IDtoMatch.ToString(), oConn);
            }

            if (Type == 2) // It's a stat overview
            {
                cmd = new SqlCommand(@"

                    select t.TrophyID, t.OverviewID, td.* 
                    from [DetailStatOverviewTrophyLink] t 
                    inner join Trophies td on t.TrophyID = td.TrophyID
                    where t.OverviewID = " + IDtoMatch.ToString(), oConn);
            }


            SqlDataAdapter da = new SqlDataAdapter();
            DataTable _returnDataTable = new DataTable();
            da.SelectCommand = cmd;
            da.Fill(_returnDataTable);

            foreach (DataRow oRow in _returnDataTable.Rows)
            {
                ServiceResources.Trophy oTrophy = MapRowToTrophy(oRow);
                string _result = Helpers.Sql.ExecuteScalar(" select count(*) From [TrophiesUserLink] where trophyID = " + oTrophy.TrophyID.ToString());
                if (_result != "")
                {
                    oTrophy.UserAwardedCount = int.Parse(_result); 
                }
                oTrophies.Add(oTrophy);
            }

            oConn.Close();
            return oTrophies;
        }


        public static ServiceResources.Trophy LoadTrophy(Int64 TrophyID)
        {


            SqlConnection oConn = new SqlConnection(LiveConnectionString);
            oConn.Open();
            // This is cheating a little bit because the code is large for what we're doing, but what the heck. So long as it works for now. 
            SqlCommand cmd = new SqlCommand("select Top 1 * from Trophies where TrophyID = " + TrophyID.ToString(), oConn);
            SqlDataAdapter da = new SqlDataAdapter();
            DataTable _returnDataTable = new DataTable();
            da.SelectCommand = cmd;
            da.Fill(_returnDataTable);

            ServiceResources.Trophy oTrophy = new ServiceResources.Trophy();

            foreach (DataRow oRow in _returnDataTable.Rows)
            {
                oTrophy = MapRowToTrophy(oRow);

            }

            return oTrophy;
        }

        public static ServiceResources.UserTrophy[] UsersWithTrophy(int TrophyID, bool complete, int RowsToReturn)
        {

            string _perComplete = "";
            if (complete)
            {
                _perComplete = " t.PercentageComplete>=100";
            }
            else
            {
                _perComplete = " t.PercentageComplete<100";
            }


            DataTable dt = Sql.ReturnDataTable(@"
                                                SELECT TOP " + RowsToReturn + @" t.UserTrophiesID
                                                      ,t.AwardedOn
                                                      ,t.PercentageComplete
                                                      ,tu.UserName, tu.UserGUID   
                                                  FROM [StartStop].[dbo].[TrophiesUserLink] t
                                                  inner join ssUsers tu on t.UserID = tu.UserID

                                                  where t.TrophyID = " + TrophyID.ToString() + " and " + _perComplete);
            if (dt.Rows.Count > 0)
            {
                int _currentRow = 0;
                ServiceResources.UserTrophy[] oTrophies = new ServiceResources.UserTrophy[dt.Rows.Count];
                foreach (DataRow oRow in dt.Rows)
                {
                    ServiceResources.UserTrophy ot = new ServiceResources.UserTrophy();
                    if (oRow["AwardedOn"].ToString() != "")
                    {
                        ot.AwardedOn = (DateTime)oRow["AwardedOn"];
                    }
                    ot.PercentageComplete = (int)oRow["PercentageComplete"];
                    ot.Username = (string)oRow["UserName"];

                    // TO DO 
                    //ot.AvatarURL = Core.UserCore.ReturnAvatarURL(new Guid(oRow["UserGUID"].ToString()), 2);
                    ot.AvatarURL = "";

                    oTrophies[_currentRow] = ot;
                    _currentRow++;
                }
                return oTrophies;
            }
            else
            {
                return null;
            }
        }

        public static ServiceResources.UserTrophy[] ReturnUserTrophy(long UserID, int MaxReturn, bool Awarded, bool FillWithNumberOfAwardsToOtherUsers)
        {
            int _awarded = 0;
            if (Awarded) { _awarded = 1; }

            string _sqlCommand = @" 
                                    SELECT  TOP " + MaxReturn.ToString() + @" 
                                          td.[TrophyID]
                                          ,t.[AwardedOn]
                                          ,t.[AwardedYet]
                                          ,t.[PercentageComplete]
                                          ,t.[CreatedOn]
                                          ,t.[ModifiedOn]
                                          ,t.[FBUpdated], td.tDescription, td.TName, td.tCondition
                                      FROM [TrophiesUserLink] t 
                                      inner join Trophies td on t.TrophyID = td.TrophyID
                                      where t.UserID = " + UserID.ToString() + @" and AwardedYet = " + _awarded;

            DataTable oTable = Sql.ReturnDataTable(_sqlCommand);
            ServiceResources.UserTrophy[] oTophies = new ServiceResources.UserTrophy[oTable.Rows.Count];
            int tCount = 0;
            foreach (DataRow oRow in oTable.Rows)
            {
                ServiceResources.UserTrophy oTrophy = new ServiceResources.UserTrophy();
                if (oRow["AwardedOn"].ToString() != "")
                {
                    oTrophy.AwardedOn = (DateTime)oRow["AwardedOn"];
                }
                oTrophy.AwardedYet = (bool)oRow["AwardedYet"];
                oTrophy.Name = (string)oRow["TName"];

                if (oRow["tDescription"] != System.DBNull.Value)
                {
                    oTrophy.Description = oRow["tDescription"].ToString(); 
                }

                oTrophy.PercentageComplete = (int)oRow["PercentageComplete"];
                if (oRow["tCondition"].ToString()!="")
                {
                    oTrophy.Condition = (string)oRow["tCondition"];
                }
                
                oTrophy.TrophyID = (int)oRow["TrophyID"];

                if (FillWithNumberOfAwardsToOtherUsers)
                {
                    oTrophy.UserAwardedCount = int.Parse(Sql.ExecuteScalar(" select count(*) From [TrophiesUserLink] where trophyID = " + oTrophy.TrophyID.ToString())); 
                }

                // Add to the main trophy model
                oTophies[tCount] = oTrophy;


                tCount++;

            }


            return oTophies;

        }

        public static ServiceResources.MessageResponse ApplicationAssociatedWithTrophy(int TrophyID)
        {
            ServiceResources.MessageResponse oResponse = new ServiceResources.MessageResponse();
            oResponse.Success = false;

            DataTable odt = Sql.ReturnDataTable(@"
                            select Title, OverviewType, OverviewID
                            from DetailStatOverview  
                            where OverviewID in (select OverviewID from  DetailStatOverviewTrophyLink where TrophyID= " + TrophyID.ToString()+")"   );

            if (odt.Rows.Count > 0)
            {
                // It exists
                if (odt.Rows.Count > 1)
                {
                    // It's multiple app link
                    oResponse.Success = true;
                    oResponse.Message = "Belongs to many";
                }
                else
                {
                    int _OverviewType = (int)odt.Rows[0]["OverviewType"];
                    // This is for PS3 Only
                    if (_OverviewType == 3)
                    {
                        oResponse.Success = true;
                        oResponse.Message = "PS3 - " + odt.Rows[0]["Title"].ToString();
                        oResponse.ReturnID = (long)odt.Rows[0]["OverviewID"];
                    }
                }
            }
            else
            {
                // TO DO! 
                // We should check for normal apps here
                
            }


            return oResponse; 
        }

        #endregion

        #region Trophy Updates

        public static ServiceResources.MessageResponse UpdateTrophy(ServiceResources.Trophy oTrophy)
        {
            ServiceResources.MessageResponse oResponse = new ServiceResources.MessageResponse();
            oResponse.Success = false;
            try
            {
                bool _isUpdate = false;

                SqlConnection oConn = new SqlConnection(LiveConnectionString);
                oConn.Open();

                SqlCommand _cmdTrophyCheck = new SqlCommand(@" Select TrophyID from Trophies where tName=@tname", oConn);
                _cmdTrophyCheck.Parameters.AddWithValue("@tname", oTrophy.Name);

                SqlCommand oCommand = new SqlCommand();

                if (_cmdTrophyCheck.ExecuteScalar() != null) // It's an update
                {

                    // Few notes on this
                    // It does not update the developerID as the creator is the only one who can do that. Makes sense, but might trip someone / something up
                    // You cannot pass the trophyID and expect it to do the update, because otherwise it's possible to overwrite someone's work

                    // TO DO
                    // Add a check to make sure the developer updating is the original developer.

                    _isUpdate = true;
                    string _result = _cmdTrophyCheck.ExecuteScalar().ToString();

                    oCommand = new SqlCommand(@"
                                                Update trophies set
                                                tName = @TrophyNAme,
                                                tDescription = @TDescription, 
                                                ImageLink = @ImageLink, 
                                                TriggerSQL = @TriggerSQL, 
                                                tCondition = @Condition, 
                                                TriggerSQLReturnValue = @TriggerSQLReturnValue, 
                                                Approved = @Approved,
                                                ModifiedOn = @ModifiedOn

                                                where TrophyID = @TrophyID
                                                ", oConn);

                    oCommand.Parameters.AddWithValue("@TrophyID", long.Parse(_result));
                    oCommand.Parameters.AddWithValue("@ModifiedOn", DateTime.Now);
                    oTrophy.TrophyID = int.Parse(_result);
                }

                else
                {
                    // Here's the insert command
                    oCommand = new SqlCommand(@"Insert into Trophies (tNAme, tDescription, tImageLink, TriggerSQL, CreatedOn, tCondition, TriggerSQLReturnValue, DeveloperID, Approved) Values
                                                                  (@TrophyNAme, @TDescription, @ImageLink, @TriggerSQL, @CreatedOn, @Condition, @TriggerSQLReturnValue, @DeveloperID, @Approved);select @@IDENTITY;", oConn);
                }

                #region map values
                if (oTrophy.Name != null)
                {
                    oCommand.Parameters.AddWithValue("@TrophyName", oTrophy.Name);
                }
                else
                {
                    oCommand.Parameters.AddWithValue("@Trophyname", System.DBNull.Value);
                }
                oCommand.Parameters.AddWithValue("@CreatedOn", DateTime.Now);
                oCommand.Parameters.AddWithValue("@TDescription", oTrophy.Description);
                if (oTrophy.ImageLink != null)
                {
                    oCommand.Parameters.AddWithValue("@ImageLink", oTrophy.ImageLink);
                }
                else
                {
                    oCommand.Parameters.AddWithValue("@ImageLink", System.DBNull.Value);
                }

                if (oTrophy.TriggerSQL != null)
                {
                    oCommand.Parameters.AddWithValue("@TriggerSQL", oTrophy.TriggerSQL);
                }
                else
                {
                    oCommand.Parameters.AddWithValue("@TriggerSQL", System.DBNull.Value);
                }

                if (oTrophy.Condition != null)
                {
                    oCommand.Parameters.AddWithValue("@Condition", oTrophy.Condition);
                }
                else
                {
                    oCommand.Parameters.AddWithValue("@Condition", System.DBNull.Value);
                }
                if (oTrophy.TriggerValue != null)
                {
                    oCommand.Parameters.AddWithValue("@TriggerSQLReturnValue", oTrophy.TriggerValue);
                }
                else
                {
                    oCommand.Parameters.AddWithValue("@TriggerSQLReturnValue", System.DBNull.Value);
                }
                oCommand.Parameters.AddWithValue("@DeveloperID", oTrophy.DeveloperID);
                oCommand.Parameters.AddWithValue("@Approved", oTrophy.Approved);
                #endregion

                if (_isUpdate)
                {
                    // Returns the trophyID
                    oResponse.ReturnID = oTrophy.TrophyID;
                }
                else
                {
                    long _return = long.Parse(oCommand.ExecuteScalar().ToString());
                    oResponse.ReturnID = _return;
                }
                oResponse.Success = true;

                oConn.Close();
            }
            catch (Exception ex)
            {
                ServiceResources.ErrorEvent oError = new ServiceResources.ErrorEvent();
                oError.ErrorDetail = ex.Message.ToString();
                oError.ErrorLevel = ServiceResources.ErrorEvent.ErrorLevelType.NotSoCritical;
                oError.ErrorMessage = "Cannot update trophy records";
                oError.Method = "Helpers.TRophy.UpdateTrophy";

                Helpers.EventLog.LogError(oError);
            }

            return oResponse;
        }

        public static ServiceResources.MessageResponse UpdateUserTrophyLink(ServiceResources.UserTrophy oUserTrophy)
        {
            ServiceResources.MessageResponse oResponse = new ServiceResources.MessageResponse();
            oResponse.Success = false;

            // Don't forget to add the update
            SqlConnection oConn = new SqlConnection(LiveConnectionString);
            oConn.Open();

            // Don't forget that we also need to do the update on this as well. 
            SqlCommand cmd = new SqlCommand(@"
                                                insert into TrophiesUserLink (UserID, TrophyID, AwardedOn, AwardedYet, PercentageComplete, CreatedOn, FBUpdated) Values
                                                                             (@UserID, @TrophyID, @AwardedOn, @AwardedYet, @PercentageComplete, @CreatedOn, @FBUpdated) ;select @@IDENTITY;
                                                ", oConn);
            cmd.Parameters.AddWithValue("@Userid", oUserTrophy.UserID);
            cmd.Parameters.AddWithValue("@TrophyID", oUserTrophy.TrophyID);
            cmd.Parameters.AddWithValue("@AwardedOn", oUserTrophy.AwardedOn);
            cmd.Parameters.AddWithValue("@AwardedYet", oUserTrophy.AwardedYet);
            cmd.Parameters.AddWithValue("@PercentageComplete", oUserTrophy.PercentageComplete);
            cmd.Parameters.AddWithValue("@CreatedOn", DateTime.Now);
            // Defaulted. 
            cmd.Parameters.AddWithValue("@FBUpdated", false);

            string _result = cmd.ExecuteScalar().ToString();
            if (_result != null)
            {
                oResponse.Success = true;
                oResponse.ReturnID = long.Parse(_result);
            }

            oConn.Close();
            return oResponse;
        }

        public static ServiceResources.MessageResponse AwardTrophy(Int64 UserID, Int64 TrophyID, DateTime AwardDate)
        {
            ServiceResources.MessageResponse oResponse = new ServiceResources.MessageResponse();
            oResponse.Success = false;

            // See if the user has the trophy yet
            string _result = StartStop.Helpers.Sql.ExecuteScalar("select UserTrophiesID from TrophiesUserLink where userID=" + UserID.ToString() + " and TrophyID = " + TrophyID.ToString());
            if (_result != "")
            {
                // It exists
                oResponse.Success = true;
                oResponse.Message = "Already exists";
                oResponse.ReturnID = long.Parse(_result);
            }
            else
            {
                // It doesn't so lets create it
                ServiceResources.UserTrophy oUTrophy = new ServiceResources.UserTrophy();

                oUTrophy.UserID = UserID;
                oUTrophy.TrophyID = TrophyID;
                oUTrophy.AwardedYet = true;
                oUTrophy.PercentageComplete = 100;
                oUTrophy.AwardedOn = AwardDate;

                ServiceResources.MessageResponse oInnerCallResponse = new ServiceResources.MessageResponse();

                // This does not send the user an email so we should add that as a seperate function? 
                oInnerCallResponse = UpdateUserTrophyLink(oUTrophy);
                if (oInnerCallResponse.Success)
                {
                    // WE might as well just map these back to each other
                    oResponse = oInnerCallResponse;
                }
            }
            return oResponse;
        }

        public static ServiceResources.MessageResponse AwardTrophyByName(Int64 UserID, string TrophyName, DateTime AwardDate)
        {
            ServiceResources.MessageResponse oResponse = new ServiceResources.MessageResponse();
            oResponse.Success = false;

            // Look up the trophyID
            SqlConnection _lookupconn = new SqlConnection(LiveConnectionString);
            _lookupconn.Open();
            SqlCommand _lookupTrophyCommand = new SqlCommand("select TrophyID from Trophies where TName=@TName", _lookupconn);
            _lookupTrophyCommand.Parameters.AddWithValue("@TName", TrophyName);



            if (_lookupTrophyCommand.ExecuteScalar() != null)
            {

                int _lookupTrophyID = int.Parse(_lookupTrophyCommand.ExecuteScalar().ToString());


                // See if the user has the trophy yet
                string _result = StartStop.Helpers.Sql.ExecuteScalar("select UserTrophiesID from TrophiesUserLink where userID=" + UserID.ToString() + " and TrophyID = " + _lookupTrophyID.ToString());
                if (_result != "")
                {
                    // It exists
                    oResponse.Success = true;
                    oResponse.Message = "Already exists";
                    oResponse.ReturnID = long.Parse(_result);
                }
                else
                {
                    // It doesn't so lets create it
                    ServiceResources.UserTrophy oUTrophy = new ServiceResources.UserTrophy();

                    oUTrophy.UserID = UserID;

                    oUTrophy.TrophyID = _lookupTrophyID;
                    oUTrophy.AwardedYet = true;
                    oUTrophy.PercentageComplete = 100;
                    oUTrophy.AwardedOn = AwardDate;

                    ServiceResources.MessageResponse oInnerCallResponse = new ServiceResources.MessageResponse();

                    // This does not send the user an email so we should add that as a seperate function? 
                    oInnerCallResponse = UpdateUserTrophyLink(oUTrophy);
                    if (oInnerCallResponse.Success)
                    {
                        // WE might as well just map these back to each other
                        oResponse = oInnerCallResponse;
                    }
                }
            }
            else
            {
                oResponse.Message = "Trophy Not Found";
            }

            _lookupconn.Close();

            return oResponse;
        }


        // TO DO ADD EMAIL CREATION! 

        public static ServiceResources.MessageResponse AssociateTrophyToDetailStat(ServiceResources.TrophyDetailStatLink oLink)
        {
            ServiceResources.MessageResponse oReturn = new ServiceResources.MessageResponse();
            oReturn.Success = false;
            SqlConnection oConn = new SqlConnection(LiveConnectionString);
            oConn.Open();

            string _alreadyexists = Sql.ExecuteScalar("select LinkID from DetailStatOverviewTrophyLink where TrophyID = " + oLink.TrophyID.ToString() + " and OverviewID = " + oLink.OverviewID.ToString());

            if (_alreadyexists == "")
            {
                SqlCommand oCommand = new SqlCommand(@"
                                                    insert into DetailStatOverviewTrophyLink (TrophyID, OverviewID, CreatedOn) 
                                                                values    (@TrophyID, @OverviewID, @CreatedOn);select @@IDENTITY; 
                                                ", oConn);

                oCommand.Parameters.AddWithValue("@TrophyID", oLink.TrophyID);
                oCommand.Parameters.AddWithValue("@OverviewID", oLink.OverviewID);
                oCommand.Parameters.AddWithValue("@CreatedOn", DateTime.Now);
                string _return = oCommand.ExecuteScalar().ToString();
                if (_return != "")
                {
                    oReturn.Success = true;
                    oReturn.ReturnID = long.Parse(_return);
                }
            }
            else
            {
                // No need to update the record. 
                // We will set the return to true, because it's not an error, just a warning. 
                oReturn.Success = true;
                oReturn.Message = "Already exists";
            }
            oConn.Close();



            return oReturn;
        }
        #endregion

    }
}
